﻿using FrameWork.Extensions;
using Microsoft.AspNetCore.Http;
using Npoi.Core.SS.UserModel;
using Npoi.Core.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace FrameWork
{
   public class ExcelHelp
   {

       public static DataSet ImportStart(HttpContext request)
       {
            var datasource = new DataSet();
            var files = request.Request.Form.Files;

            if (files.Count == 0)
            {
                throw new IException("文件不能为空");
            }
            var fileType = new[] { ".xls", ".xlsx", ".csv" };
            for (var i = 0; i < files.Count; i++)
            {
                var file = files[i];
                if (file == null || file.Length <= 0)
                {
                    throw new IException("文件不能为空");
                }
                var filename = Path.GetFileName(file.FileName);
                var filesize = file.Length;
                var extension = Path.GetExtension(filename);
                if (string.IsNullOrWhiteSpace(extension)) throw new IException("获取文件扩展名失败");
                var fileEx = extension.ToLower();
                const int maxsize = 4000 * 1024;

                if (!fileType.Contains(fileEx))
                {
                    throw new IException("文件类型错误，只能导入" + string.Join(",", fileType) + "格式的文件");
                }

                if (filesize < maxsize) continue;
                throw new IException("上传文件超过4M，不能上传");
            }

            for (var i = 0; i < files.Count; i++)
            {
                var file = files[i];

                if (file == null) continue;
                var tempdata = ImportExcel(file.OpenReadStream(), Path.GetExtension(Path.GetFileName(file.FileName)));

                for (var j = 0; j < tempdata.Tables.Count; j++)
                {
                    var temptable = tempdata.Tables[j].Copy();
                    var tableName = Path.GetFileName(file.FileName);

                    if (tempdata.Tables.Count > 1)
                    {
                        tableName += "_" + j;
                    }

                    temptable.TableName = tableName;
                    datasource.Tables.Add(temptable);
                }
            }

            return datasource;
        }

        /// <summary>
        /// 读取excel流到table
        /// </summary>
        /// <param name="excelstream"></param>
        /// <param name="suffixname"></param>
        /// <returns></returns>
        public static DataSet ImportExcel(Stream excelstream, string suffixname = ".xlsx")
        {
            var dataset = new DataSet();

            IWorkbook hssfworkbook;


            if (suffixname.ToLower().Equals(".csv"))
            {
                var dt = new DataTable();
                var sr = new StreamReader(excelstream, Encoding.Default);
                string strLine;
                var isFirst = true;
                var columnCount = 0;
                while ((strLine = sr.ReadLine()) != null)
                {
                    var line = strLine.Split(',');
                    if (isFirst)
                    {
                        isFirst = false;
                        columnCount = line.Length;
                        foreach (var str in line)
                        {
                            dt.Columns.Add(str);
                        }
                    }
                    else
                    {
                        var dr = dt.NewRow();
                        for (var j = 0; j < columnCount; j++)
                        {
                            if (line.Length > j)
                            {
                                dr[j] = PublicTool.FormatStr(line[j]);
                            }
                            else
                            {
                                dr[j] = "";
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }

                dataset.Tables.Add(dt);
                return dataset;
            }

            try
            {
                hssfworkbook = WorkbookFactory.Create(excelstream);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            for (var i = 0; i < hssfworkbook.NumberOfSheets; i++)
            {
                var sheet = hssfworkbook.GetSheetAt(i);
                var rows = sheet.GetRowEnumerator();
                var dt = new DataTable();

                if (sheet.LastRowNum <= 0)
                {
                    continue;
                }

                rows.MoveNext();
                var row = (IRow)rows.Current;

                for (var j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
                    dt.Columns.Add(row.GetCell(j).ToString());
                }

                while (rows.MoveNext())
                {
                    row = (IRow)rows.Current;
                    var dr = dt.NewRow();
                    for (var k = 0; k < row.LastCellNum; k++)
                    {
                        var cell = row.GetCell(k);
                        if (cell == null)
                        {
                            dr[k] = "";
                        }
                        else
                        {
                            dr[k] = PublicTool.FormatStr(cell.ToString());
                        }
                    }
                    dt.Rows.Add(dr);
                }

                dataset.Tables.Add(dt);
            }
            return dataset;
        }
        /// <summary>  
        /// 转化一个DataTable  
        /// </summary>  

        /// <typeparam name="T"></typeparam>  
        /// <param name="list"></param>  
        /// <returns></returns>  
        public static DataTable ToDataTable<T>(IEnumerable<T> list)
        {

            //创建属性的集合  
            var pList = new List<PropertyInfo>();
            //获得反射的入口  

            var type = typeof(T);
            var dt = new DataTable();
            //把所有的public属性加入到集合 并添加DataTable的列  
            Array.ForEach(type.GetProperties(), p =>
            {
                pList.Add(p);
                dt.Columns.Add(p.Name, Generictype(p.PropertyType));
            });
            foreach (var item in list)
            {
                //创建一个DataRow实例  
                var row = dt.NewRow();
                //给row 赋值  
                pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
                //加入到DataTable  
                dt.Rows.Add(row);
            }
            return dt;
        }


        /// <summary>  
        /// DataTable 转换为List 集合  
        /// </summary>
        /// <param name="dt">DataTable</param>  
        /// <returns></returns>  
        public static List<T> ToList<T>(DataTable dt) where T : class, new()
        {
            //创建一个属性的列表  
            var prlist = new List<PropertyInfo>();
            //获取TResult的类型实例  反射的入口  

            var t = typeof(T);

            //获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表   
            Array.ForEach(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });

            //创建返回的集合  

            var oblist = new List<T>();

            foreach (DataRow row in dt.Rows)
            {
                //创建TResult的实例  
                var ob = new T();
                //找到对应的数据  并赋值  
                prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
                //放入到返回的集合中.  
                oblist.Add(ob);
            }
            return oblist;
        }




        /// <summary>  
        /// 将集合类转换成DataTable  
        /// </summary>  
        /// <param name="list">集合</param>  
        /// <returns></returns>  
        public static DataTable ToDataTableTow(IList list)
        {
            var result = new DataTable();
            if (list.Count <= 0) return result;
            var propertys = list[0].GetType().GetProperties();

            foreach (var pi in propertys)
            {
                result.Columns.Add(pi.Name, Generictype(pi.PropertyType));
            }
            foreach (var t in list)
            {
                var tempList = new ArrayList();
                foreach (var pi in propertys)
                {
                    var obj = pi.GetValue(t, null);
                    tempList.Add(obj);
                }
                var array = tempList.ToArray();
                result.LoadDataRow(array, true);
            }
            return result;
        }


        /**/

        /// <summary>  
        /// 将泛型集合类转换成DataTable  
        /// </summary>  
        /// <typeparam name="T">集合项类型</typeparam>  
        /// <param name="list">集合</param>
        /// <param name="result"></param>
        /// <returns>数据集(表)</returns>  
        public static DataTable ToDataTable<T>(IList<T> list, DataTable result = null)
        {
            return ToDataTable(list, result, null);

        }


        /**/

        /// <summary>  
        /// 将泛型集合类转换成DataTable  
        /// </summary>  
        /// <typeparam name="T">集合项类型</typeparam>  
        /// <param name="list">集合</param>
        /// <param name="result"></param>
        /// <param name="propertyName">需要返回的列的列名</param>  
        /// <returns>数据集(表)</returns>  
        public static DataTable ToDataTable<T>(IList<T> list, DataTable result = null, params string[] propertyName)
        {
            if (result == null)
            {
                result = new DataTable();
            }
            var propertyNameList = new List<string>();
            if (propertyName != null)
                propertyNameList.AddRange(propertyName);
            if (list.Count <= 0) return result;
            var propertys = list[0].GetType().GetProperties();

            if (result.Columns.Count == 0)
            {
                foreach (var pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        result.Columns.Add(pi.Name, Generictype(pi.PropertyType));
                    }
                    else
                    {
                        if (propertyNameList.Contains(pi.Name))
                            result.Columns.Add(pi.Name, Generictype(pi.PropertyType));
                    }
                }
            }

            foreach (var t in list)
            {
                var tempList = new ArrayList();
                foreach (var pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        var obj = pi.GetValue(t, null);
                        tempList.Add(obj);
                    }
                    else
                    {
                        if (!propertyNameList.Contains(pi.Name)) continue;
                        var obj = pi.GetValue(t, null);
                        tempList.Add(obj);
                    }
                }
                var array = tempList.ToArray();
                result.LoadDataRow(array, true);
            }
            return result;
        }
        private static Type Generictype(Type propertyType)
        {
            var type = typeof(string);
            var typeName = propertyType.ToString();

            if (typeName.Contains("["))
            {
                typeName = typeName.Split('[')[1].Split(']')[0];
            }
            switch (typeName)
            {
                case "System.Int32":
                    type = typeof(int);
                    break;
                case "System.Int64":
                    type = typeof(long);
                    break;
                case "System.DateTime":
                    type = typeof(DateTime);
                    break;
                case "System.Decimal":
                    type = typeof(decimal);
                    break;
                case "System.Double":
                    type = typeof(double);
                    break;
            }
            return type;
        }
        /// <summary>
        /// 将table转换为excel流
        /// </summary>
        /// <param name="dataset"></param>
        /// <returns></returns>
        public static byte[] ExportExcel(DataSet dataset)
        {
            var book = new XSSFWorkbook();

            for (var j = 0; j < dataset.Tables.Count; j++)
            {
                var table = dataset.Tables[j];
                var sheet = book.CreateSheet("Sheet" + j + 1.ToString());

                var head = sheet.CreateRow(0);
                for (var i = 0; i < table.Columns.Count; i++)
                {
                    head.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                }

                for (var i = 0; i < table.Rows.Count; i++)
                {

                    var row = sheet.CreateRow(i + 1);
                    var rowsarray = table.Rows[i].ItemArray;
                    for (var k = 0; k < table.Columns.Count; k++)
                    {

                        if (rowsarray[k] is byte[])
                        {
                            var bitmap = rowsarray[k] as byte[];

                            var ms = new MemoryStream(bitmap);

                            var pictureIdx = book.AddPicture(ms, XSSFWorkbook.PICTURE_TYPE_JPEG);

                            ms.Close();

                            var drawing = row.Sheet.CreateDrawingPatriarch();

                            var helper = book.GetCreationHelper();
                            var anchor = helper.CreateClientAnchor();

                            anchor.Col1 = k;

                            var pict = drawing.CreatePicture(anchor, pictureIdx);

                            pict.Resize();

                        }
                        else
                        {
                            row.CreateCell(k).SetCellValue(rowsarray[k].ToString());
                        }


                    }
                }
            }

            using (var ms = new MemoryStream())
            {
                book.Write(ms);
                return ms.ToArray();
            }
        }
    }
}
